package scct.zhanghongbo.dao;

import scct.zhanghongbo.model.Staff;
import scct.zhanghongbo.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StaffDao {
    private static Staff getStaff(ResultSet rs) throws SQLException {
        return new Staff(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("gender"),
                rs.getString("phone"),
                rs.getString("account"),
                rs.getString("password"),
                rs.getInt("staff_rank"),
                rs.getInt("eh_id")
        );
    }
    public Staff login(String account, String password) {
        Staff staff = null;
        String sql = "select * from cr_staff where account=? and password=?";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, account);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                staff = getStaff(rs);
            }
            DBUtils.close(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return staff;
    }


    public List<Staff> selectAll() {
        List<Staff> list = new ArrayList<Staff>();
        String sql = "select * from cr_staff";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(getStaff(rs));
            }
            DBUtils.close(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    public boolean isExistence(String account,String phone) {
        String sql = "select * from cr_staff where account=? and phone=?";
        Integer count = getCount(account, phone, sql);
        return count > 0;
    }

    public boolean isExistence(String account,String phone,Integer id) {
        String sql = "select * from cr_staff where account=? and phone=? and id!=?";
        Integer count = getCount(account, phone, sql);
        return count > 0;
    }

    private static Integer getCount(String account, String phone, String sql) {
        Integer count = 0;
        Connection conn = DBUtils.getConnection();
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, account);
            ps.setString(2, phone);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            DBUtils.close(conn);
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
        return count;
    }

    public boolean insert(String staffNameText, String gender, String staffPhoneText, String staffAccountText, String staffPassText, Integer rankValue, Integer ehId) {
        Integer count = 0;
        String sql = "insert into cr_staff values(null,?,?,?,?,?,?,?)";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, staffNameText);
            ps.setString(2, gender);
            ps.setString(3, staffPhoneText);
            ps.setString(4, staffAccountText);
            ps.setString(5, staffPassText);
            ps.setInt(6, rankValue);
            ps.setInt(7, ehId);
            count += ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count > 0;
    }

    public List<Staff> search(String key, String value) {
        List<Staff> list = new ArrayList<Staff>();
        String sql = "select * from cr_staff where " + key + " = ?";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,value);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                list.add(getStaff(rs));
            }
            DBUtils.close(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    public Staff getStaffById(Integer id) {
        Staff staff = null;
        String sql = "select * from cr_staff where id = ?";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                staff = getStaff(rs);
            }
            DBUtils.close(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return staff;
    }

    public boolean deleteRowById(Integer id) {
        Integer count = 0;
        String sql = "delete from cr_staff where id = ?";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            count += ps.executeUpdate();
            DBUtils.close(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count > 0;
    }
}
